import pymysql
import xlrd

db = pymysql.connect(host="192.168.0.26", user="root", password="root", database="test", port=3306)
cursor = db.cursor()
sql = "INSERT INTO `test`.`college`(`id`, `name`, `city`, `type`) VALUES (%s, %s, %s, %s);"
select_sql = "SELECT MAX(id) FROM college;"

with xlrd.open_workbook("大湾区入库名单.xlsx", encoding_override="utf-8") as f:
    for sheet in f.sheet_names():
        names = []
        type = sheet

        cursor.execute(select_sql)
        index = cursor.fetchone()[0]

        table = f.sheet_by_name(sheet)
        for i in range(0, table.nrows):
            row = table.row_values(rowx=i)
            name = '"' + str(row[0]) + '" '
            index += 1
            tmp = (index, name, None, type)
            names.append(tmp)
        cursor.executemany(sql, names)
        db.commit()
